//
//  XJSqlite3ModelTool.swift
//  XJSqlite3Tool
//
//  Created by shanlin on 2017/7/1.
//  Copyright © 2017年 善林(中国)金融信息服务有限公司. All rights reserved.
//
/*
1:  关于工具类的封装
2:  实现方案2
01: 基于配置
02: 运行时动态获取
*/

/** 模型工具类所有操作:
 
 1:func createTableDynamic => 动态建立模型表
 2:func isTableRequiredUpdata => 判断是非需要更新模型表
 3:func updateTable => 如果需要更新模型表、更新模型表
 4:func saveOrupdateModel => 模型存储、修改更新操作
 5:func deleteModel => 删除模型表（4中方式）
 6:func queryAllModels => 查询模型表结果集(3种方式)
 */



enum RelationType: String {
    case RelationTypeEqual = "="
    case RelationTypeMore = ">"
    case RelationTypeLess = "<"
    case RelationTypeMoreEqual = ">="
    case RelationTypeLessEqual = "<="
}

import UIKit
class XJSqlite3ModelTool: NSObject {}




//============================================================
//       Check 1 - 根据一个模型类, 动态创建模型数据库表
//============================================================
/**
 根据一个模型类, 动态创建模型数据库表
 
 @param cls 类名
 @param uid 用户唯一标识
 @return 是否创建成功
 */
extension XJSqlite3ModelTool {
    class func createTableDynamic<T: NSObject>(cls: T.Type, uid: String) -> Bool where T: XJSqlite3ModelProtocol  {
        // 1.创建表格的sql语句给拼接出来
        // 尽可能多的自己获取的自己去获取、实在判断不了用户意图的、只能让用户来告诉
        // create table if not exists 表名(字段1 字段1类型（约束）,字段2 字段2类型（约束）,......,primary key(字段))
        // let obj = T.self   （T.Type:传类方法   T:传对象方法）

        // 1.1获取表名称
        let clsName = XJSqlite3ModelManager.xj_getClassName(cls: cls)
        
        // 1.2校验是否实现了主键的方法
        if !cls.responds(to: #selector(cls.setupPrimaryKey)) {
            print("*************要想动态模型建表、需要首先实现模型中主键的方法: static func setupPrimaryKey(){} 来告诉我主键信息")
            return false
        }
        
        let primaryKey = cls.setupPrimaryKey()
        let columnNames = XJSqlite3ModelManager.xj_getAllColumnNameWithSored(cls: cls)
        if !columnNames.contains(primaryKey) {
            print("*************主键设置信息有误、请重新确认！")
            return false
        }

        // 1.3获取一个模型里面的所有变量和类型
        //create table if not exists XMGStu(name text,b integer,stuNum integer,age integer,score real, primary key(stuNum))
        let createTableSql = "create table if not exists \(clsName)(\(XJSqlite3ModelManager.xj_columnNameTypeDict(cls: cls)), primary key(\(cls.setupPrimaryKey())))"
        
        
        // 2.执行直接在XJSqlite3Tool调用即可
        return XJSqlite3Tool.share.xj_deal(sql: "\(createTableSql)", uid: uid)
    }
}




//============================================================
//       Check 2 - 判断一个模型动态新建表格是否需要更新
//============================================================
/**
 判断一个模型动态新建表格是否需要更新
 
 @param cls 类名
 @param uid 用户唯一标识
 @return 模型动态建表是否需要更新
 */
extension XJSqlite3ModelTool {
    class func isTableRequiredUpdata<T: NSObject>(cls: T.Type, uid: String) -> Bool where T: XJSqlite3ModelProtocol{
        
        // 1 获取模型中的所有属性（排过序）
        let modelNames = XJSqlite3ModelManager.xj_getAllColumnNameWithSored(cls: cls)
        
        // 2 获取表中所有的字段名（排过序）
        let tableNames = XJSqlite3TableTool.xj_getTableColumnNamesWithSorted(cls: cls, uid: uid)
        
        print("modelNames=\(modelNames)")
        print("tableNames=\(tableNames)")
        return !(modelNames == tableNames)
    }
}




//============================================================
//       Check 3 - 更新动态模型新建表格
//============================================================
/**
 更新动态模型新建表格
 
 @param cls 类名
 @param uid 用户唯一标识
 @return 是否更新成功
 */
extension XJSqlite3ModelTool {
    class func updateTable<T: NSObject>(cls: T.Type, uid: String) -> Bool where T: XJSqlite3ModelProtocol{
        // 1: 创建一个临时表
        let clsName = XJSqlite3ModelManager.xj_getClassName(cls: cls)
        let clsTmpName = clsName + "_tmp"
        
        
        if !cls.responds(to: #selector(cls.setupPrimaryKey)) {
            print("*************要想动态模型建表、需要首先实现模型中主键的方法: static func setupPrimaryKey(){} 来告诉我主键信息")
            return false
        }
        
        let primaryKey = cls.setupPrimaryKey()
        let columnNames = XJSqlite3ModelManager.xj_getAllColumnNameWithSored(cls: cls)
        if !columnNames.contains(primaryKey) {
            print("*************主键设置信息有误、请重新确认！")
            return false
        }
        
        // 添加事务
        var execSqls: [String] = [String]()
    
        let createTableSql = "create table if not exists \(clsTmpName)(\(XJSqlite3ModelManager.xj_columnNameTypeDict(cls: cls)), primary key(\(cls.setupPrimaryKey())))"
        execSqls.append(createTableSql)
        
        // 2: 首先插入主键数据 insert into XJModel_temp(id) select id from XJModel
        let insertPrimaryKeyDataSql = "insert into \(clsTmpName)(\(cls.setupPrimaryKey())) select \(cls.setupPrimaryKey()) from \(clsName)"
        execSqls.append(insertPrimaryKeyDataSql)
        
        // 3: 根据主键，插入其他存在数据: update XJModel_temp set name = (select name from XJModel where XJModel_temp.id = XJModel.id)
        let oldColumnNamesArray = XJSqlite3TableTool.xj_getTableColumnNamesWithSorted(cls: cls, uid: uid)
        let newColumnNamesArray = XJSqlite3ModelManager.xj_getAllColumnNameWithSored(cls: cls)
        
        // 3.1 得到修改字段映射表字典
        var renameDict = [String : String]()
        if cls.responds(to: #selector(cls.newNameToOldName)) {
            renameDict = cls.newNameToOldName!()
        }
        
        for columnName in newColumnNamesArray {
            
            var oldName = columnName
            // 通过模型中的字段找映射表字段是否有
            let dictName = renameDict[columnName] ?? ""
            if dictName.characters.count != 0 {
                oldName = renameDict[columnName]!
            }
            
            if (!oldColumnNamesArray.contains(columnName) && !oldColumnNamesArray.contains(oldName)) || columnName == cls.setupPrimaryKey(){
                continue
            }
            //update 临时表 set 新的字段名字 = (select 旧的字段名字 from 旧表 where 临时表.id = 旧表.id)
            let updateOtherDataSql = "update \(clsTmpName) set \(columnName) = (select \(oldName) from XJModel where \(clsTmpName).\(cls.setupPrimaryKey()) = \(clsName).\(cls.setupPrimaryKey()))"
            execSqls.append(updateOtherDataSql)
        }
        

        // 4: 删除旧表
        let deleteOldTableSql = "drop table if exists \(clsName)"
        execSqls.append(deleteOldTableSql)
        
        // 5: 修改新表名字
        let renameTableNameSql = "alter table \(clsTmpName) rename to \(clsName)"
        execSqls.append(renameTableNameSql)
        
        // 6: 执行多项sql语句
        return XJSqlite3Tool.share.xj_dealSqls(sqls: execSqls, uid: uid)
    }
}




// MARK: ******** 模型存新操作 ********

//============================================================
//       Check 3 - (保存模型、修改模型)(增、改)
//============================================================
/**
 (保存模型、修改模型)(增、改)
 
 @param cls 类名
 @param uid 用户唯一标识
 @return 是否保存成功
 */
extension XJSqlite3ModelTool {
    // 如果用户再使用过程中, 直接调用这个方法, 去保存模型
    class func saveOrupdateModel<T: NSObject>(obj: T, uid: String) -> Bool where T: XJSqlite3ModelProtocol{
        // 1: 判断表格是否存在、不存在直接创建
        
        let cls = obj.classForCoder as! T.Type
        if !XJSqlite3TableTool.isTableExists(cls: obj.classForCoder, uid: uid){
           _ = self.createTableDynamic(cls: cls, uid: uid)
        }
        
        // 2: 判断表格是否需要更新、
        if XJSqlite3ModelTool.isTableRequiredUpdata(cls: cls, uid: uid) {
           _ = self.updateTable(cls: cls, uid: uid)
        }
        
        // 3:判断字段里是否存在主键
        let tableName = XJSqlite3ModelManager.xj_getClassName(cls: obj.classForCoder)
        
        if !cls.responds(to: #selector(cls.setupPrimaryKey)) {
            print("*************要想动态模型建表、需要首先实现模型中主键的方法: static func setupPrimaryKey(){} 来告诉我主键信息")
            return false
        }
        let primaryKey = cls.setupPrimaryKey()
        let columnNames = XJSqlite3ModelManager.xj_getAllColumnNameWithSored(cls: cls)
        if !columnNames.contains(primaryKey) {
            print("*************主键设置信息有误、请重新确认！")
            return false
        }
        
        let primaryKeyValue = obj.value(forKeyPath: primaryKey) ?? ""
        let checkSql = "select * from \(tableName) where \(primaryKey) = '\(primaryKeyValue)'"
        let resultArray = XJSqlite3Tool.share.xj_querySql(sql: checkSql, uid: uid)
        
        // 对应的字段值
        var columnValues: [String] = [String]()
        for name in columnNames {
            let value = obj.value(forKeyPath: name) ?? ""
            let str = self.returnStringValue(value: value)
            columnValues.append(str)
        }
        // update XMGStu set stuNum='1',score='0',age2='100',name='李胜兵'  where stuNum = '1'
        // update 表名 set 字段1=字段1值,字段2=字段2的值... where 主键 = '主键值'
        // insert into 表名(字段1, 字段2, 字段3) values ('值1', '值2', '值3')
        var setValueArray = [String]()
        for i in 0..<columnNames.count {
            let name = columnNames[i]
            let value = columnValues[i]
            let setStr = "\(name) = '\(value)'"
            setValueArray.append(setStr)
        }
        
        // 5: 更新
        var execSql: String = ""
        if resultArray.count > 0 {
            // 第二次更新数据
            execSql = "update \(tableName) set \(setValueArray.joined(separator: ","))  where \(primaryKey) = '\(primaryKeyValue)'"
        }else {
            // 第一次插入数据
            execSql = "insert into \(tableName)(\(columnNames.joined(separator: ","))) values('\(columnValues.joined(separator: "','"))')"
        }
        
        return XJSqlite3Tool.share.xj_deal(sql: execSql, uid: uid)
    }
    
    
    //============================================================
    //       Check 3.1 - 通过转一个any类型的值转化为字符串的值出去【数组、字典、整型、浮点型等】
    //============================================================
    //  - 通过转一个any类型的值转化为字符串的值出去【数组、字典、整型、浮点型等】
    fileprivate class func returnStringValue(value: Any) -> String {
        
        var value = value
        if let valueNumber = value as? NSNumber {    //整型
            value = valueNumber.stringValue
        }else if let valueArray = value as? NSArray {   //数组
            value = self.getJSONStringFromOther(obj: valueArray)
        }else if let valueDict = value as? NSDictionary { //字典
            value =  self.getJSONStringFromOther(obj: valueDict)
        }else if let valueData = value as? NSData {        //二进制数据
            value = NSString(data: valueData as Data, encoding: String.Encoding.utf8.rawValue) ?? ""
        }
        return value as? String ?? ""
    }
    
    //============================================================
    //       Check 3.2 -传一个对象、转成JSONString-> string
    //============================================================
    fileprivate class func getJSONStringFromOther(obj: NSObject) -> String {
        if (!JSONSerialization.isValidJSONObject(obj)) {
            print("无法解析出JSONString")
            return ""
        }
        let data : NSData! = try? JSONSerialization.data(withJSONObject: obj, options: []) as NSData!
        let JSONString = NSString(data:data as Data,encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
}




// MARK: ******* 模型删除操作 *******

//============================================================
//       Check 4.1 -删除模型（1）: 根据主键来删除 model
//============================================================
/**
 删除模型（1）: 根据主键来删除 model
 
 @param cls 类名
 @param uid 用户唯一标识
 @return 是否删除模型成功
 */
extension XJSqlite3ModelTool {
    class func deleteModel<T: NSObject>(obj: T, uid: String) -> Bool where T: XJSqlite3ModelProtocol{
        let cls = obj.classForCoder as! T.Type
        
        // 3:判断字段里是否存在主键
        let tableName = XJSqlite3ModelManager.xj_getClassName(cls: obj.classForCoder)
        if !cls.responds(to: #selector(cls.setupPrimaryKey)) {
            print("*************要想动态模型建表、需要首先实现模型中主键的方法: static func setupPrimaryKey(){} 来告诉我主键信息")
            return false
        }
        let primaryKey = cls.setupPrimaryKey()
        let columnNames = XJSqlite3ModelManager.xj_getAllColumnNameWithSored(cls: cls)
        if !columnNames.contains(primaryKey) {
           print("*************主键设置信息有误、请重新确认！")
            return false
        }
        
        let primaryKeyValue = obj.value(forKeyPath: primaryKey) ?? ""
        let deleteSql = "delete from \(tableName) where \(primaryKey) = '\(primaryKeyValue)'"
        return XJSqlite3Tool.share.xj_deal(sql: deleteSql, uid: uid)
    }
}




//============================================================
//       Check 4.2 -删除模型（2）
//============================================================
/**
 删除模型（2）: 根据sql delete from 表 where score > '10' and name = '李胜兵'
 
 @param cls 类名
 @param sql 完整语句sql拼写
 @param uid 用户唯一标识
 @return 是否删除模型成功
 */
extension XJSqlite3ModelTool {
    class func deleteModelWithSql(cls: AnyClass, uid: String, deleteSql: String) -> Bool {
        return XJSqlite3Tool.share.xj_deal(sql: deleteSql, uid: uid)
    }
}




//============================================================
//       Check 4.3 -删除模型（3）
//============================================================
/**
 删除模型（3）: 根据条件来删除 "score = '89'"
 
 @param cls 类名
 @param whereStr 条件语句后整个字段字符串
 @param uid 用户唯一标识
 @return 是否删除模型成功
 */
extension XJSqlite3ModelTool {
    class func deleteModelWhereSqlStr(cls: AnyClass, whereStr: String, uid: String) -> Bool {
        let tableName = XJSqlite3ModelManager.xj_getClassName(cls: cls)
        var deleteSql = "delete from \(tableName)"
        if whereStr.characters.count > 0 {
            deleteSql = deleteSql + " where \(whereStr)"
        }
        return XJSqlite3Tool.share.xj_deal(sql: deleteSql, uid: uid)
    }
}





//============================================================
//       Check 4.4 -删除模型（4）
//============================================================
/**
 删除模型（4）: 根据关系条件来删除 score >= '10'
 
 @param cls 类名
 @param whereStr 条件语句后三个字符串
 @param uid 用户唯一标识
 @return 是否删除模型成功
 */
extension XJSqlite3ModelTool {
    class func deleteModelWhereRelationSql(cls: AnyClass, uid: String, columnName: String, relation: RelationType, value: Any) -> Bool {
        let tableName = XJSqlite3ModelManager.xj_getClassName(cls: cls)
        let deleteSql = "delete from \(tableName) where \(columnName) \(relation.rawValue) '\(value)'"
        return XJSqlite3Tool.share.xj_deal(sql: deleteSql, uid: uid)
    }
}






// MARK: ******* 模型查询操作 *******


//============================================================
//       Check 5.1 -查询模型（1）: 类、查询该模型中所有的记录）
//============================================================
/**
 查询模型（1）: 类、查询该模型中所有的记录
 
 @param cls 类名
 @param uid 用户唯一标识
 @return [Any] 查询结果【数组】
 */
extension XJSqlite3ModelTool {
    class func queryAllModels<T: NSObject>(cls: T.Type, uid: String) -> [Any] where T: XJSqlite3ModelProtocol{
        
        // 1: sql
        //let cls = obj.classForCoder as! T.Type
        
        let tableName = XJSqlite3ModelManager.xj_getClassName(cls: cls)
        let sql = "select * from \(tableName)"
        return self.dealQueryModelResults(cls: cls, sql: sql, uid: uid)
    }
}





//============================================================
//       Check 5.2 -查询模型（2）: 根据条件来查询
//============================================================
/**
 查询模型（2）: 根据条件来查询
 
 @param cls 类名
 @param uid 用户id
 @param columnName 字段名字
 @param relation 不同的关系 枚举
 @param value 条件约束值
 @return [any]
 */
extension XJSqlite3ModelTool {
    class func queryAllModelsWithWhereSql<T: NSObject>(cls: T.Type, uid: String, columnName: String, relation: RelationType, value: Any) -> [Any] where T: XJSqlite3ModelProtocol {
        let tableName = XJSqlite3ModelManager.xj_getClassName(cls: cls)
        let sql = "select * from \(tableName) where \(columnName) \(relation.rawValue) '\(value)'"
        return self.dealQueryModelResults(cls: cls, sql: sql, uid: uid)
    }
}






//============================================================
//       Check 5.3 - 根据完整sql来查询结果集 <如多表联查需要根据自己的sql语句来查询>
//============================================================
/**
 查询模型（3）: 根据完整sql来查询结果集 <如多表联查需要根据自己的sql语句来查询>
 
 @param cls 类名
 @param uid 用户id
 @param querySql 完整sql、复杂sql语句完整拼写
 @return [any]
 */
extension XJSqlite3ModelTool {
    class func queryAllModelsWithSql<T: NSObject>(cls: T.Type, uid: String, sql: String) -> [Any] where T: XJSqlite3ModelProtocol {
        return self.dealQueryModelResults(cls: cls, sql: sql, uid: uid)
    }
}






//============================================================
//       Check 6 - 查询私有方法：处理结果集
//============================================================
extension XJSqlite3ModelTool {
    /// 处理查询模型的结果集
    fileprivate class func dealQueryModelResults<T: NSObject>(cls: T.Type, sql: String, uid: String) -> [Any] where T:XJSqlite3ModelProtocol{
        // 2: 执行查询
        let resultArrayDict = XJSqlite3Tool.share.xj_querySql(sql: sql, uid: uid)
        
        // 3:获取所有模型中字段 + 类型
        let columnTypedict = XJSqlite3ModelManager.xj_getIvarNameTypeDictFromClass(cls: cls)
        
        // 4: 处理查询的结果集 -> 模型数组
        var models = [Any]()
        for dict in resultArrayDict {
            let modelObjc = cls.self.init()
            
            
            for (key,obj) in dict {
                var resultValue = obj
                let type = columnTypedict[key] ?? ""
                print("type=====\(type)")
                
                if type == "NSArray"{
                    // 字符串 -> 
                    let data = (obj as? String ?? "").data(using: String.Encoding.utf8) ?? Data()
                    let array = try? JSONSerialization.jsonObject(with: data, options: .mutableContainers)
                    if array != nil {
                       resultValue = array as? NSArray ?? []
                    }
                }else if type == "NSDictionary" {
                    // 字符串 ->
                    let data = (obj as? String ?? "").data(using: String.Encoding.utf8) ?? Data()
                    let array = try? JSONSerialization.jsonObject(with: data, options: .mutableContainers)
                    if array != nil {
                        resultValue = array as? NSDictionary ?? [:]
                    }
                }else if type == "NSData" {
                    resultValue = (resultValue as! String).data(using: String.Encoding.utf8) ?? ""
                }
                modelObjc.setValue(resultValue, forKeyPath: key)
            }
            models.append(modelObjc)
        }
        return models
    }
}

